﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;
using System.Data.Common;

namespace SQLToClass
{
    public partial class frmMain : Form
    {
        private DBConfig _dbConfig = null;

        public frmMain()
        {
            InitializeComponent();
        }

        #region form events
        private void frmMain_Load(object sender, EventArgs e)
        {
            cboDBTypes.SelectedIndex = 0;
            //load db configuration
            _dbConfig = DBConfig.Load();
            if (_dbConfig != null)
            {
                cboDBTypes.SelectedIndex = _dbConfig.LastDBActiveWorking;
                SelectDbTypeChange();
                LoadDataInformation();
            }
        }

        private void frmMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (_dbConfig != null)
            {
                _dbConfig.LastDBActiveWorking = cboDBTypes.SelectedIndex;
            }
            //save db config when close form
            DBConfig.Save(_dbConfig);
        }
        #endregion

        #region Control events
        private void btnLoad_Click(object sender, EventArgs e)
        {
            LoadDataInformation();
        }

        private void lswTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            lswDataInfo.Items.Clear();
            if (lswTables.SelectedItems.Count > 0)
            {
                TableInfo tblInfo = (TableInfo)lswTables.SelectedItems[0].Tag;
                foreach (var item in tblInfo.Columns)
                {
                    ListViewItem li = lswDataInfo.Items.Add(item.ColumnName);
                    li.SubItems.Add(item.DataType);
                    li.SubItems[1].ForeColor = Color.Blue;
                }
            }
        }

        private void btnGenerate_Click(object sender, EventArgs e)
        {
            try
            {
                string errors = null;
                if (string.IsNullOrWhiteSpace(txtNamespace.Text))
                {
                    errors = "Please enter your namespace";
                    txtNamespace.Focus();
                }

                if (string.IsNullOrWhiteSpace(txtDirectory.Text))
                {
                    errors += (errors == null ? "" : "\n") + "Please select your directory output";
                    txtDirectory.Focus();
                }

                if (string.IsNullOrEmpty(errors))
                {
                    //create directory
                    string dir = txtDirectory.Text;

                    if (!Directory.Exists(dir))
                    {
                        Directory.CreateDirectory(dir);
                    }

                    foreach (ListViewItem item in lswTables.CheckedItems)
                    {
                        TableInfo tbInfo = (TableInfo)item.Tag;
                        string path = Path.Combine(dir, tbInfo.TableName + ".cs");
                        string text = GenerateClass(tbInfo);
                        using (StreamWriter writer = new StreamWriter(path, false))
                        {
                            writer.Write(text);
                            writer.Close();
                        }
                    }

                    //save data info
                    SaveConfigIntoDBConfig();
                }
                else
                {
                    MessageBox.Show(errors, "Validate fail", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void btnOpenFolder_Click(object sender, EventArgs e)
        {
            string dir = Path.Combine(Application.StartupPath, "Temp");
            if (!Directory.Exists(dir))
            {
                MessageBox.Show("Not found directory", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                Process.Start("explorer.exe", dir);
            }
        }

        private void btnBrowseOutputDirectory_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dlg = new FolderBrowserDialog();
            if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtDirectory.Text = dlg.SelectedPath;
            }
        }

        private void IsIntegratedSecurity_CheckedChanged(object sender, EventArgs e)
        {
            bool disable = chkIsIntegratedSecurity.Checked;
            txtUserName.ReadOnly = disable;
            txtPassword.ReadOnly = disable;
        }

        private void cboDBTypes_SelectedIndexChanged(object sender, EventArgs e)
        {
            SelectDbTypeChange();
        }
        #endregion

        #region Database Interaction
        private List<TableInfo> GetAllTablesInDB()
        {
            List<TableInfo> lstTablesInfo = null;
            DbCommand cmd = Connection.GetCommand(GetConnectionType(), GetConnectionString());
            try
            {
                cmd.Connection.Open();
                DataView dtView = cmd.Connection.GetSchema("Tables").DefaultView;
                //dtView.RowFilter = "TABLE_TYPE = 'BASE TABLE'";
                dtView.Sort = "TABLE_NAME ASC";
                DataTable dt = dtView.ToTable();

                lstTablesInfo = new List<TableInfo>();
                foreach (DataRow row in dt.Rows)
                {
                    TableInfo tbl = new TableInfo();
                    tbl.TableName = row["TABLE_NAME"].ToString();
                    tbl.Columns = new List<ColumnInfo>();

                    cmd.CommandText = "SELECT * FROM " + tbl.TableName + " WHERE '1' = '2'";
                    DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

                    //Retrieve column schema into a DataTable.
                    DataTable schemaTable = reader.GetSchemaTable();

                    //For each field in the table...
                    foreach (DataRow rColumn in schemaTable.Rows)
                    {
                        //Display the field name and value.
                        tbl.Columns.Add(new ColumnInfo { ColumnName = rColumn["ColumnName"].ToString(), DataType = GetDataType(rColumn["DataType"].ToString(), (bool)rColumn["AllowDBNull"]), IsKey = (bool)rColumn["IsKey"], IsAutoIncrement = (bool)rColumn["IsAutoIncrement"] });
                    }

                    //Always close the DataReader and connection.
                    reader.Close();
                    lstTablesInfo.Add(tbl);
                }

                cmd.Connection.Close();
            }
            catch (DbException e)
            {
                MessageBox.Show(e.Message, "Error while connect to DB", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch
            {

            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                {
                    cmd.Connection.Close();
                }
            }
            return lstTablesInfo;
        }

        private string GetDataType(string rawDataType, bool isAllowDBNull)
        {
            string dataType = rawDataType;
            switch (rawDataType)
            {
                case "System.String":
                    dataType = "string";
                    break;
                case "System.Int32":
                    dataType = "int" + (isAllowDBNull ? "?" : "");
                    break;
                case "System.Int64":
                    dataType = "long" + (isAllowDBNull ? "?" : "");
                    break;
                case "System.Double":
                    dataType = "double" + (isAllowDBNull ? "?" : "");
                    break;
                case "System.Decimal":
                    dataType = "decimal" + (isAllowDBNull ? "?" : "");
                    break;
                case "System.Guid":
                    dataType = "Guid";
                    break;
                case "System.Byte[]":
                    dataType = "byte[]";
                    break;
                case "System.DateTime":
                    dataType = "DateTime" + (isAllowDBNull ? "?" : "");
                    break;
                case "System.Boolean":
                    dataType = "bool" + (isAllowDBNull ? "?" : "");
                    break;
                default:
                    break;
            }
            return dataType;
        }

        private string GenerateClass(TableInfo tableInfo)
        {
            string modifier = "public";
            bool isWCF = radWCFClass.Checked;
            bool isMandatory = chkIsMandatory.Checked;
            string tab = "\t";
            string endLine = "\r\n";
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Linq;");
            sb.AppendLine("using System.Text;");
            //sb.AppendLine("using Benchmark.Services.DataModel.Attributes;");
            sb.AppendLine("using Messenger.DataAccess.Attributes;");            
            if (isWCF)
            {
                sb.AppendLine("using System.Runtime.Serialization;");
            }
            sb.AppendLine();
            sb.AppendFormat("namespace {0}", txtNamespace.Text);
            sb.AppendLine(endLine + "{");
            if (isWCF)
            {
                sb.AppendLine(tab + "[DataContract]");
            }
            sb.AppendFormat(tab + modifier + " class {0}", tableInfo.TableName);
            sb.Append(endLine + tab + "{");
            foreach (ColumnInfo column in tableInfo.Columns)
            {
                if (column.IsKey)
                {
                    sb.Append(endLine + tab + tab + "[PrimaryKey]");
                }
                if (column.IsAutoIncrement)
                {
                    sb.Append(endLine + tab + tab + "[AutoIncrement]");
                }
                if (isMandatory)
                {
                    sb.Append(endLine + tab + tab + "[MandatoryValidation]");
                }
                if (isWCF)
                {
                    sb.Append(endLine + tab + tab + "[DataMember]");
                }
                sb.AppendFormat(endLine + tab + tab + "public {0} {1} {{ get; set; }}", column.DataType, column.ColumnName);
            }
            sb.AppendLine(endLine + tab + "}");
            sb.AppendLine("}");
            return sb.ToString();
        }

        /// <summary>
        /// Get connection selected to database
        /// </summary>
        /// <returns></returns>
        private ConnectionType GetConnectionType()
        {
            ConnectionType connType = ConnectionType.SqlServer;
            switch (cboDBTypes.SelectedIndex)
            {
                case 0:
                    connType = ConnectionType.SqlServer;
                    break;
                case 1:
                    connType = ConnectionType.MySql;
                    break;
                default:
                    break;
            }
            return connType;
        }

        /// <summary>
        /// Get connection string from parameter in form
        /// </summary>
        /// <returns></returns>
        private string GetConnectionString()
        {
            string sqlConnecotionString = "";
            if (chkIsIntegratedSecurity.Checked && chkIsIntegratedSecurity.Enabled)
            {
                sqlConnecotionString = string.Format("Server={0};Database={1};Integrated Security=SSPI", txtServer.Text, txtDBName.Text);
            }
            else
            {
                sqlConnecotionString = string.Format("Server={0};Database={1};User={2};Password={3}", txtServer.Text, txtDBName.Text, txtUserName.Text, txtPassword.Text);
            }
            return sqlConnecotionString;
        }
        #endregion

        #region Helpers
        /// <summary>
        /// show configuration in form
        /// </summary>
        /// <param name="config"></param>
        private void ShowConfigurationToForm(ConfigBase config)
        {
            chkIsIntegratedSecurity.Enabled = true;
            if (config != null)
            {
                txtServer.Text = config.Server;
                txtDBName.Text = config.Database;
                txtUserName.Text = config.Username;
                txtPassword.Text = config.Password;
                chkIsMandatory.Checked = config.IsMandatory;
                if (config.IsPlainClass)
                {
                    radPlainClass.Checked = true;
                }
                else
                {
                    radWCFClass.Checked = true;
                }
                txtNamespace.Text = config.Namespace;
                txtDirectory.Text = config.OutputDirectory;
                if (config is SqlConfig)
                {
                    SqlConfig sqlConfig = (SqlConfig)config;
                    chkIsIntegratedSecurity.Checked = sqlConfig.IsIntegratedSecurity;
                    txtUserName.ReadOnly = sqlConfig.IsIntegratedSecurity;
                    txtPassword.ReadOnly = sqlConfig.IsIntegratedSecurity;
                }
                else if (config is ConfigBase)
                {
                    chkIsIntegratedSecurity.Checked = false;
                    chkIsIntegratedSecurity.Enabled = false;
                    txtUserName.ReadOnly = false;
                    txtPassword.ReadOnly = false;
                }
            }
            else
            {
                if (cboDBTypes.SelectedIndex == 1)//my sql
                {
                    chkIsIntegratedSecurity.Checked = false;
                    chkIsIntegratedSecurity.Enabled = false;
                    txtUserName.ReadOnly = false;
                    txtPassword.ReadOnly = false;
                }
            }
        }

        private void SaveConfigIntoDBConfig()
        {
            if (_dbConfig == null)
            {
                _dbConfig = new DBConfig();
            }
            ConfigBase config = null;
            if (cboDBTypes.SelectedIndex == 0)
            {
                //save sql config
                config = new SqlConfig();
                _dbConfig.SqlConfig = (SqlConfig)config;
            }
            else
            {
                config = new ConfigBase();
                _dbConfig.MySqlConfig = config;
            }
            SaveConfigurationToMemory(config);

        }

        private void SaveConfigurationToMemory(ConfigBase config)
        {
            config.Server = txtServer.Text;
            config.Database = txtDBName.Text;
            config.Username = txtUserName.Text;
            config.Password = txtPassword.Text;
            config.IsMandatory = chkIsMandatory.Checked;
            config.IsPlainClass = radPlainClass.Checked;
            config.Namespace = txtNamespace.Text;
            config.OutputDirectory = txtDirectory.Text;
            if (config is SqlConfig)
            {
                SqlConfig sqlConfig = (SqlConfig)config;
                sqlConfig.IsIntegratedSecurity = chkIsIntegratedSecurity.Checked;
            }
        }

        private void LoadDataInformation()
        {
            lswTables.Items.Clear();
            lswDataInfo.Items.Clear();
            List<TableInfo> data = GetAllTablesInDB();
            if (data != null)
            {
                //save config when load table information success
                SaveConfigIntoDBConfig();
                //fetch data in to listview
                foreach (var item in data)
                {
                    ListViewItem li = lswTables.Items.Add(item.TableName);
                    li.Tag = item;
                }
            }
        }

        private void SelectDbTypeChange()
        {
            //load config in memory
            if (_dbConfig != null)
            {
                ConfigBase config = null;
                if (cboDBTypes.SelectedIndex == 0)//sql server
                {
                    config = _dbConfig.SqlConfig;
                }
                else if (cboDBTypes.SelectedIndex == 1) //my sql server
                {
                    config = _dbConfig.MySqlConfig;
                }

                ShowConfigurationToForm(config);
            }
        }
        #endregion



    }
}
